package personal.study.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import personal.study.form.SupplierForm;

public class DBSupplier {
	private static Log log = LogFactory.getLog(DBSupplier.class);

	public Collection<SupplierForm> querySupplier(Connection con,
			String conditions, int currentPage, int rowsPerPage)
			throws Exception {
		Collection<SupplierForm> col = new ArrayList<SupplierForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("SupplierCode,");
		buffer.append("SupplierName,");
		buffer.append("SupplierAddress,");
		buffer.append("SupplierPhoneNo,");
		buffer.append("SupplierAgent,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM Suppliers WHERE ");
		buffer.append(conditions);
		buffer.append(" LIMIT ");
		buffer.append((currentPage-1)*rowsPerPage);
		buffer.append(",");
		buffer.append(rowsPerPage);

		log.debug(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		SupplierForm supplierForm = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				supplierForm = new SupplierForm();
				supplierForm.setSupplierCode(rs.getString("SupplierCode"));
				supplierForm.setSupplierName(rs.getString("SupplierName"));
				supplierForm
						.setSupplierAddress(rs.getString("SupplierAddress"));
				supplierForm
						.setSupplierPhoneNo(rs.getString("SupplierPhoneNo"));
				supplierForm.setSupplierAgent(rs.getString("SupplierAgent"));
				supplierForm.setFlag(rs.getString("Flag"));
				supplierForm.setRemark(rs.getString("Remark"));
				col.add(supplierForm);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return col;
	}

	public int getCount(Connection con, String conditions) throws Exception {
		int count = 0;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("SELECT COUNT(*) FROM Suppliers WHERE ");
		buffer.append(conditions);

		log.debug(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}
		return count;
	}
	
	public boolean insert(Connection con, SupplierForm supplierForm) throws Exception {
		boolean result = false;
		
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("INSERT INTO Suppliers (");
		buffer.append("SupplierCode,");
		buffer.append("SupplierName,");
		buffer.append("SupplierAddress,");
		buffer.append("SupplierPhoneNo,");
		buffer.append("SupplierAgent,");
		buffer.append("Flag,");
		buffer.append("Remark) ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
			debugBuffer.append(buffer.toString());
			debugBuffer.append("VALUES (");
			debugBuffer.append("'").append(supplierForm.getSupplierCode()).append("',");
			debugBuffer.append("'").append(supplierForm.getSupplierName()).append("',");
			debugBuffer.append("'").append(supplierForm.getSupplierAddress()).append("',");
			debugBuffer.append("'").append(supplierForm.getSupplierPhoneNo()).append("',");
			debugBuffer.append("'").append(supplierForm.getSupplierAgent()).append("',");
			debugBuffer.append("'").append(supplierForm.getFlag()).append("',");
			debugBuffer.append("'").append(supplierForm.getRemark()).append("'");
			log.debug(debugBuffer.toString());
		}
		
		buffer.append("VALUES (?,?,?,?,?,?,?)");
		
		PreparedStatement ps = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, supplierForm.getSupplierCode());
			ps.setString(2, supplierForm.getSupplierName());
			ps.setString(3, supplierForm.getSupplierAddress());
			ps.setString(4, supplierForm.getSupplierPhoneNo());
			ps.setString(5, supplierForm.getSupplierAgent());
			ps.setString(6, supplierForm.getFlag());
			ps.setString(7, supplierForm.getRemark());
			ps.executeUpdate();
			result = true;
		}catch(Exception e){
			log.error(e.getMessage());
			result = false;
		}finally{
			ps.close();
		}
		
		return result;
	}
	
	public SupplierForm findByPrimaryKey(Connection con, String supplierCode) throws Exception{
		SupplierForm supplierForm = null;
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("SupplierCode,");
		buffer.append("SupplierName,");
		buffer.append("SupplierAddress,");
		buffer.append("SupplierPhoneNo,");
		buffer.append("SupplierAgent,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM Suppliers WHERE ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
			debugBuffer.append(buffer.toString());
			debugBuffer.append("SupplierCode=");
			debugBuffer.append("'").append(supplierCode).append("'");
			log.debug(debugBuffer.toString());
		}
		
		buffer.append("SupplierCode=?");
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, supplierCode);
			rs = ps.executeQuery();
			if(rs.next()){
				supplierForm = new SupplierForm();
				supplierForm.setSupplierCode(rs.getString("SupplierCode"));
				supplierForm.setSupplierName(rs.getString("SupplierName"));
				supplierForm
						.setSupplierAddress(rs.getString("SupplierAddress"));
				supplierForm
						.setSupplierPhoneNo(rs.getString("SupplierPhoneNo"));
				supplierForm.setSupplierAgent(rs.getString("SupplierAgent"));
				supplierForm.setFlag(rs.getString("Flag"));
				supplierForm.setRemark(rs.getString("Remark"));
			}
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
			rs.close();
		}
		return supplierForm;
	}
	
	public boolean update(Connection con, SupplierForm supplierForm) throws Exception{
		boolean result = false;
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("UPDATE Suppliers SET ");
		buffer.append("SupplierName=?,");
		buffer.append("SupplierAddress=?,");
		buffer.append("SupplierPhoneNo=?,");
		buffer.append("SupplierAgent=?,");
		buffer.append("Flag=?,");
		buffer.append("Remark=? ");
		
		if(log.isDebugEnabled()){
			
		}
		
		buffer.append("WHERE ");
		buffer.append("SupplierCode=?");
		
		PreparedStatement ps = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, supplierForm.getSupplierName());
			ps.setString(2, supplierForm.getSupplierAddress());
			ps.setString(3, supplierForm.getSupplierPhoneNo());
			ps.setString(4, supplierForm.getSupplierAgent());
			ps.setString(5, supplierForm.getFlag());
			ps.setString(6, supplierForm.getRemark());
			ps.setString(7, supplierForm.getSupplierCode());
			ps.executeUpdate();
			result = true;
		}catch(Exception e){
			log.error(e.getMessage());
			result = false;
		}finally{
			ps.close();
		}
		return result;
	}
	
	public void delete(Connection con, String supplierCode) throws Exception{
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("DELETE FROM Suppliers WHERE ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(100);
			debugBuffer.append(buffer.toString());
			debugBuffer.append("");
		}
		
		buffer.append("SupplierCode=?");
		
		PreparedStatement ps = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, supplierCode);
			ps.executeUpdate();
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
		}
	}
}
